IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetClinicalTrialBudgetTotals]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetClinicalTrialBudgetTotals]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE dbo.GetClinicalTrialBudgetTotals (@budgetId int) AS
BEGIN
    SET NOCOUNT ON

    DECLARE @overheadRate decimal(4,2), @overheadRateCode varchar(256), @institutionCode varchar(256)
    SELECT @institutionCode = InstitutionCode, @overheadRateCode = OverheadRateCode FROM ClinicalTrialBudget WHERE Id = @budgetId
    IF @overheadRateCode = 'ClinicalTrialBudgetOverheadRateOther'
    BEGIN
        SELECT @overheadRate = OtherNegotiatedRate / 100 FROM ClinicalTrialBudget WHERE Id = @budgetId
    END
    ELSE
    BEGIN
        SELECT @overheadRate = CAST (a.Value AS DECIMAL(4,2)) / 100 FROM RefCodeAttribute a WHERE a.RefCode = @overheadRateCode AND a.RefDomain='ClinicalTrialBudgetOverheadRate'
    END

    SELECT 'Tasks - ' + CASE WHEN s.StudyStaffRoleCode = 'ClinicalTrialBudgetStaffRoleOther' THEN s.OtherRoleText ELSE r.refmeaning END Role, 
        ISNULL( SUM( t.taskcost ), 0 ) TotalPerSubject,
        ISNULL( SUM( CAST ( (t.taskcost * (CASE WHEN t.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
        ISNULL( SUM( CAST ( (t.taskcost * b.enrolledsubjects) AS DECIMAL(16,2)) ), 0 ) Total,
        ISNULL( SUM( CAST ( (t.taskcost * b.enrolledsubjects * (CASE WHEN t.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead
    FROM clinicaltrialbudget b, clinicaltrialbudgettask t, clinicaltrialbudgetstaff s, refcode r, clinicaltrialbudgetmilestonevisit bmv
        WHERE b.Id = @budgetId
            AND t.clinicaltrialbudgetid = b.id
            AND s.id = t.clinicaltrialbudgetstaffid
            AND r.refdomain = 'ClinicalTrialBudgetStaffRole'
            AND r.refcode = s.studystaffrolecode
            AND bmv.relateclass = 'BudgetTask'
            AND bmv.relateid = t.id
            AND bmv.required = 1
            AND bmv.standardofcare = 0
        GROUP BY t.clinicaltrialbudgetstaffid, s.StudyStaffRoleCode, r.refmeaning, s.OtherRoleText

    SELECT 'Tasks - Total' Role,
        ISNULL( SUM( t.taskcost ), 0 ) TotalPerSubject,
        ISNULL( SUM( CAST ( (t.taskcost * (CASE WHEN t.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
        ISNULL( SUM( CAST ( (t.taskcost * b.enrolledsubjects) AS DECIMAL(16,2)) ), 0 ) Total,
        ISNULL( SUM( CAST ( (t.taskcost * b.enrolledsubjects * (CASE WHEN t.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead
    FROM clinicaltrialbudget b, clinicaltrialbudgettask t, clinicaltrialbudgetstaff s, refcode r, clinicaltrialbudgetmilestonevisit bmv
        WHERE b.Id = @budgetId
            AND t.clinicaltrialbudgetid = b.id
            AND s.id = t.clinicaltrialbudgetstaffid
            AND r.refdomain = 'ClinicalTrialBudgetStaffRole'
            AND r.refcode = s.studystaffrolecode
            AND bmv.relateclass = 'BudgetTask'
            AND bmv.relateid = t.id
            AND bmv.required = 1
            AND bmv.standardofcare = 0

    SELECT'Professional Fees' Role,
        ISNULL( SUM( CAST ( (p.professionalfee) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubject,
        ISNULL( SUM( CAST ( (p.professionalfee * (CASE WHEN p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
        ISNULL( SUM( CAST ( (p.professionalfee * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Total,
        ISNULL( SUM( CAST ( (p.professionalfee * b.enrolledsubjects * (CASE WHEN p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead
    FROM clinicaltrialbudget b, clinicaltrialbudgetprocedure p, clinicaltrialbudgetmilestonevisit bmv
    WHERE b.id = @budgetId
        AND p.clinicaltrialbudgetid = b.id
        AND bmv.relateclass = 'BudgetProcedure'
        AND bmv.relateid = p.id
        AND bmv.required = 1
        AND bmv.standardofcare = 0

    IF (@institutionCode = 'ClinicalTrialBudgetInstitutionBWH')
        BEGIN
        SELECT
            'Inpatient Procedures' Role,
            ISNULL( SUM( CAST ( (p.procedurecost) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubject,
            ISNULL( SUM( CAST ( (p.procedurecost * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Total,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Actual,
            ISNULL( SUM( CAST ( (b.enrolledsubjects * ISNULL(bcf.Price, 0) * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2))), 0) TotalResearchRateWithOverhead
        FROM clinicaltrialbudget b, clinicaltrialbudgetprocedure p, clinicaltrialbudgetmilestonevisit bmv, bwh_cdmfeed bcf
        WHERE b.Id = @budgetId
            AND p.clinicaltrialbudgetid = b.id
            AND p.Inpatient = 1 
            AND bmv.relateclass = 'BudgetProcedure'
            AND bmv.relateid = p.id
            AND bmv.required = 1
            AND bmv.standardofcare = 0
            AND bcf.ChargeCode = p.ProcedureChargeCode

        SELECT
            'Outpatient Procedures' Role,
            ISNULL( SUM( CAST ( (p.procedurecost) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubject,
            ISNULL( SUM( CAST ( (p.procedurecost * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Total,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Actual,
            ISNULL( SUM( CAST ( (b.enrolledsubjects * ISNULL(bcf.Price, 0) * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2))), 0) TotalResearchRateWithOverhead
        FROM clinicaltrialbudget b, clinicaltrialbudgetprocedure p, clinicaltrialbudgetmilestonevisit bmv, bwh_cdmfeed bcf
        WHERE b.Id = @budgetId
            AND p.clinicaltrialbudgetid = b.id
            AND p.Inpatient = 0
            AND bmv.relateclass = 'BudgetProcedure'
            AND bmv.relateid = p.id
            AND bmv.required = 1
            AND bmv.standardofcare = 0
            AND bcf.ChargeCode = p.ProcedureChargeCode
        END
    ELSE
        BEGIN
        SELECT
            'Inpatient Procedures' Role,
            ISNULL( SUM( CAST ( (p.procedurecost) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubject,
            ISNULL( SUM( CAST ( (p.procedurecost * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Total,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Actual,
            ISNULL( SUM( CAST ( (b.enrolledsubjects * (CASE WHEN mcf.ResearchRate IS NOT NULL THEN mcf.Price * mcf.ResearchRate/100 ELSE ISNULL(mcf.Price, 0) END) * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2))), 0) TotalResearchRateWithOverhead
        FROM clinicaltrialbudget b, clinicaltrialbudgetprocedure p, clinicaltrialbudgetmilestonevisit bmv, mgh_cdmfeed mcf
        WHERE b.Id = @budgetId
            AND p.clinicaltrialbudgetid = b.id
            AND p.Inpatient = 1 
            AND bmv.relateclass = 'BudgetProcedure'
            AND bmv.relateid = p.id
            AND bmv.required = 1
            AND bmv.standardofcare = 0
            AND mcf.ChargeCode = p.ProcedureChargeCode

        SELECT 'Outpatient Procedures' Role,
            ISNULL( SUM( CAST ( (p.procedurecost) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubject,
            ISNULL( SUM( CAST ( (p.procedurecost * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalPerSubjectWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Total,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) ), 0 ) TotalWithOverhead,
            ISNULL( SUM( CAST ( (p.procedurecost * b.enrolledsubjects) AS DECIMAL(16,2) ) ), 0 ) Actual,
            ISNULL( SUM( CAST ( (b.enrolledsubjects * (CASE WHEN mcf.ResearchRate IS NOT NULL THEN mcf.Price * mcf.ResearchRate/100 ELSE ISNULL(mcf.Price, 0) END) * (CASE when p.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2))), 0) TotalResearchRateWithOverhead
        FROM clinicaltrialbudget b, clinicaltrialbudgetprocedure p, clinicaltrialbudgetmilestonevisit bmv, mgh_cdmfeed mcf
        WHERE b.Id = @budgetId
        AND p.clinicaltrialbudgetid = b.id
        AND p.Inpatient = 0
        AND bmv.relateclass = 'BudgetProcedure'
        AND bmv.relateid = p.id
        AND bmv.required = 1
        AND bmv.standardofcare = 0
        AND mcf.ChargeCode = p.ProcedureChargeCode
    END

    SELECT 'Other Study Charges' Role, null TotalPerSubject, null TotalPerSubjectWithOverhead, CAST( SUM( expensecost ) AS DECIMAL(16,2) ) Total, CAST( SUM( expensecost * (CASE when e.applyoverhead = 1 THEN 1 + @overheadRate ELSE 1 END)) AS DECIMAL(16,2) ) TotalWithOverhead
    FROM clinicaltrialbudget b, clinicaltrialbudgetexpense e 
    WHERE b.id=@budgetId AND e.clinicaltrialbudgetid = b.id AND e.VariableCost=0

END
GO
